package com.zxh.blog.util.excelDemo;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder;
import org.apache.poi.xssf.usermodel.XSSFSheet;

import java.awt.Color;
import java.io.OutputStream;
import java.util.List;

/**
 * @Author:wb-zxh410423
 * @Description:qwdwqe
 * @Date: Created in 16:43 2018/12/7
 */
public class ExcelDemo {

	public static <T> void excelWrite(OutputStream out,List<T> dataList,Class<T> clazz) throws Exception {
		//set Sheet页头部
		XSSFWorkbook xWorkbook = new XSSFWorkbook();
		try {
			XSSFSheet sheet = xWorkbook.createSheet("sheet1");
			ExcelData excelData = new ExcelData(clazz, dataList);

			setSheetHeader(xWorkbook,sheet,excelData.getHeaderRow());
			//set Sheet页内容
			setSheetContent(xWorkbook, sheet, excelData, clazz);
			autoSizeColumns(sheet, excelData.getHeaderRow().size() + 1);
			xWorkbook.write(out);
			out.flush();
		}finally {
			if(out != null) {
				out.close();
			}
		}
	}

	/**
	 * 配置(赋值)表格内容部分
	 * @param xWorkbook
	 * @param sheet
	 * @throws Exception
	 */
	private static <T> void setSheetHeader(XSSFWorkbook xWorkbook, XSSFSheet sheet, List<String> titles){
		//创建内容样式（头部以下的样式）
		XSSFCellStyle titleStyle = xWorkbook.createCellStyle();
		titleStyle.setWrapText(true);
		Font titleFont = xWorkbook.createFont();
		titleFont.setFontName("simsun");
		titleFont.setColor(IndexedColors.BLACK.index);
		//设置水平垂直居中
		titleStyle.setAlignment(HorizontalAlignment.CENTER);
		titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		titleStyle.setFillForegroundColor(new XSSFColor(new Color(182, 184, 192)));
		titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		titleStyle.setFont(titleFont);
		setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));

		int rowIndex = 0;
		int colIndex = 0;
		Row titleRow = sheet.createRow(rowIndex);
		for (String field : titles) {
			Cell cell = titleRow.createCell(colIndex);
			cell.setCellValue(field);
			cell.setCellStyle(titleStyle);
			colIndex++;
		}
	}

	/**
	 * 配置Excel表格的顶部信息，如：学号  姓名  年龄  出生年月
	 * @param wb
	 * @param sheet
	 */
	private static <T> void setSheetContent(XSSFWorkbook wb, XSSFSheet sheet,ExcelData excelData,Class<T> tClass) {
		int rowIndex = 1;
		Font dataFont = wb.createFont();
		dataFont.setFontName("simsun");
		dataFont.setColor(IndexedColors.BLACK.index);
		XSSFCellStyle dataStyle = wb.createCellStyle();
		dataStyle.setAlignment(HorizontalAlignment.CENTER);
		dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		dataStyle.setFont(dataFont);
		setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));
		//格式兼容不下来，
		List<List<String>> rows = excelData.getRows(tClass);
		for (List<String> rowData : rows) {
			int colIndex = 0;
			Row row = sheet.createRow(rowIndex);
			for(String cellData : rowData) {
				Cell cell = row.createCell(colIndex);
				cell.setCellValue(cellData);
				cell.setCellStyle(dataStyle);
				colIndex++;
			}
			rowIndex++;
		}
	}

	/**
	 * 设置size
	 * @param sheet
	 * @param columnNumber
	 */
	private static void autoSizeColumns(Sheet sheet, int columnNumber) {

		for (int i = 0; i < columnNumber; i++) {
			int orgWidth = sheet.getColumnWidth(i);
			sheet.autoSizeColumn(i, true);
			int newWidth =  sheet.getColumnWidth(i) + 100;
			if (newWidth > orgWidth) {
				sheet.setColumnWidth(i, newWidth);
			} else {
				sheet.setColumnWidth(i, orgWidth);
			}
		}
	}

	/**
	 * 设置单元格边框
	 * @param style
	 * @param border
	 * @param color
	 */
	private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {
		style.setBorderTop(border);
		style.setBorderLeft(border);
		style.setBorderRight(border);
		style.setBorderBottom(border);
		style.setBorderColor(XSSFCellBorder.BorderSide.TOP, color);
		style.setBorderColor(XSSFCellBorder.BorderSide.LEFT, color);
		style.setBorderColor(XSSFCellBorder.BorderSide.RIGHT, color);
		style.setBorderColor(XSSFCellBorder.BorderSide.BOTTOM, color);
	}
}
